package com.info.zhiduoduo.repository.mapper.company;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.project.LongProjectBriefDto;
import com.info.zhiduoduo.repository.dto.project.LongProjectDto;
import com.info.zhiduoduo.repository.entity.company.LongTermProject;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author Bob
 * @since 2021-07-05
 */
public interface LongTermProjectMapper extends BaseMapper<LongTermProject> {


    @Select("select cp.*,c.compname,c.compemail,c.creditcode,us.username,us.specialinvoice,us.invoicescope," +
            " ca.business_manager_id,bm.business_manager as businessManagerName,pay.pay_name,pay.pay_name_alias " +
            " from zdd_long_term_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
            " join zdd_maker_studio us on us.userid = cp.userid " +
            " join zdd_company_account ca on ca.compid = cp.compid " +
            " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " left join zdd_payment pay on cp.pay_id = pay.id " +
            " where cp.projectid = #{projectid}")
    LongProjectDto getLongProjectDetail(@Param("projectid") Integer projectid);

    @Select(" select cp.projectid,cp.projectname,c.compid,c.compname,us.userid,us.username,ma.realname," +
            " cp.categoryid,spc.projectcategory as categoryname,cp.projectamount,cp.paid_amount," +
            " cp.projectcycle,cp.projectfinish,cp.invoicetype,cp.projectstart,cp.expire_date, " +
            " cp.state,cp.createtime,cp.pay_id,cp.party_id,pay.pay_name,pay.pay_name_alias " +
            " from zdd_long_term_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
            " join zdd_maker_studio us on us.userid = cp.userid " +
            " join zdd_maker_account ma on ma.userid = cp.userid " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
            " left join zdd_payment pay on pay.id = cp.pay_id " +
            " where if(#{compid}=0, 1=1, cp.compid = #{compid} )" +
            " and if(#{userid}=0, 1=1, cp.userid = #{userid}) " +
            " and if(#{state}=0, 1=1, cp.state = #{state}) " +
            " and if(#{categoryid} = 0 , 1=1, cp.categoryid = #{categoryid}) " +
            " and DATE(cp.createtime) between DATE(#{startDate}) and DATE(#{endDate}) " +
            " and concat(IFNULL(cp.projectname,''),IFNULL(c.compname,''),IFNULL(us.username,''),IFNULL(ma.realname,'')) like '%${name}%' " +
            " and cp.deleted = 0 " +
            " order by cp.state,cp.createtime desc ")
    List<LongProjectBriefDto> getProjectBriefListByCompid(@Param("compid") int compid, @Param("userid") int userid,@Param("name") String name, @Param("categoryid") int categoryid, @Param("state") int state,
                                                          @Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select(" select SUM(cp.projectamount) AS projectamount ,SUM(cp.paid_amount) AS paidAmount " +
            " from zdd_long_term_project cp " +
            " join zdd_company c on c.compid = cp.compid " +
            " join zdd_maker_studio us on us.userid = cp.userid " +
            " join zdd_maker_account ma on ma.userid = cp.userid " +
            " left join zdd_system_projectcategory spc on spc.projectcategoryid = cp.categoryid " +
            " where if(#{compid}=0, 1=1, cp.compid = #{compid} )" +
            " and if(#{userid}=0, 1=1, cp.userid = #{userid}) " +
            " and if(#{state}=0, 1=1, cp.state = #{state}) " +
            " and if(#{categoryid} = 0 , 1=1, cp.categoryid = #{categoryid}) " +
            " and DATE(cp.createtime) between DATE(#{startDate}) and DATE(#{endDate}) " +
            " and concat(IFNULL(cp.projectname,''),IFNULL(c.compname,''),IFNULL(us.username,''),IFNULL(ma.realname,'')) like '%${name}%' " +
            " and cp.deleted = 0 " )
    LongProjectBriefDto listStatistical(@Param("compid") int compid, @Param("userid") int userid,@Param("name") String name, @Param("categoryid") int categoryid, @Param("state") int state,
                                              @Param("startDate") Date startDate, @Param("endDate") Date endDate);

}
